<?php
class Search_Model_Selekiname extends Apm_Model_Base{

    /**
     * 駅一覧データ検索
     *
     * @access    public
     * @param     array
     * @return    Array 駅一覧データ
     */
    public function getEkinameList($params)
    {
        $db_params = array();
        $sql = $this->generateEkinameSql($params, $db_params);
        return $this->dbGetList($sql, $db_params);
    }

    /**
     * 駅一覧データ検索用SQL文の取得
     *
     * @access    private
     * @param     array
     * @param     array ※参照渡し
     * @return    string 駅データ検索用SQL
     */
    private function generateEkinameSql($params, &$db_params)
    {
        $db_params['stWord'] = $this->convParam($params);
        $sql = "";
            $sql .= "SELECT";
            $sql .= "    T2.TODOUFUKEN_CD AS TODOUFUKEN_CD";
            $sql .= "    ,M43.M43_TODOUFUKEN AS TODOUFUKEN";
            $sql .= "    ,T2.RENRAKU_EKI_CD AS RENRAKU_EKI_CD";
            $sql .= "    ,T2.EKI_CD AS EKI_CD";
            $sql .= "    ,T2.EKI_NAME AS EKI_NAME";
            $sql .= "    ,NVL(T1.EKI_CNT,0) AS REC_CNT";
            $sql .= "    ,T2.ENSEN_CD AS ENSEN_CD";
            $sql .= "    ,T2.ENSEN_NAME AS ENSEN_NAME";
            $sql .= "    ,T2.IDO AS IDO";
            $sql .= "    ,T2.KEIDO AS KEIDO";
            $sql .= "  FROM";
            $sql .= "    (";
            $sql .= "        SELECT";
            $sql .= "            M03_TODOUFUKEN_CD as TODOUFUKEN_CD";
            $sql .= "            ,M03_TODOUFUKEN as TODOUFUKEN";
            $sql .= "            ,M03_ENSEN_CD as ENSEN_CD";
            $sql .= "            ,M03_ENSEN_NAME as ENSEN_NAME";
            $sql .= "            ,M03_EKI_CD as EKI_CD";
            $sql .= "            ,M03_EKI_NAME as EKI_NAME";
            $sql .= "            ,NVL(M03_RENRAKU_EKI_CD,M03_EKI_CD) AS RENRAKU_EKI_CD";
            $sql .= "            ,M03_IDO as IDO";
            $sql .= "            ,M03_KEIDO as KEIDO";
            $sql .= "          FROM";
            $sql .= "            M03EKI";
            $sql .= "         WHERE";
            $sql .= "            (UPPER(to_multi_byte(M03_EKI_NAME)) LIKE '%' || :stWord || '%'";
            $sql .= "            OR UPPER(to_multi_byte(M03_EKI_NAME)) LIKE '%' || :stWord || '駅%')";
        if ('11' == $params['SrhKbn'])
        {
            // 地図検索
            $sql .= "            AND M03_IDO IS NOT NULL";
            $sql .= "            AND M03_KEIDO IS NOT NULL";
        }
            $sql .= "    ) T2";
            $sql .= "    ,(";
            $sql .= "        SELECT";
            $sql .= "            T54_EKI_CNT as EKI_CNT";
            $sql .= "            ,T54_EKI_CD as EKI_CD";
            $sql .= "          FROM";
            $sql .= "            T54EKI_SUM";
            $sql .= "         WHERE";
            $sql .= "            T54_DATA_KUBUN = 0";
            $sql .= "    ) T1";
            $sql .= "    ,M43TODOUFUKEN M43";
            $sql .= " WHERE";
            $sql .= "    T2.TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD";
            $sql .= "    AND T2.EKI_CD = T1.EKI_CD(+)";
            $sql .= " GROUP BY";
            $sql .= "    T2.TODOUFUKEN_CD";
            $sql .= "    ,M43.M43_TODOUFUKEN";
            $sql .= "    ,T2.RENRAKU_EKI_CD";
            $sql .= "    ,T2.EKI_CD";
            $sql .= "    ,T2.EKI_NAME";
            $sql .= "    ,T1.EKI_CNT";
            $sql .= "    ,T2.ENSEN_CD";
            $sql .= "    ,T2.ENSEN_NAME";
            $sql .= "    ,T2.IDO";
            $sql .= "    ,T2.KEIDO";
            $sql .= " ORDER BY";
            $sql .= "    TODOUFUKEN_CD ASC";
            $sql .= "    ,RENRAKU_EKI_CD ASC";
            $sql .= "    ,EKI_NAME ASC";
        return $sql;
    }

   /**
    * 駅一覧データを表示用に変換
    *
    * @access    public
    * @param    array ※参照渡し
    * @return    void
    */
    public function convertEkinameData(&$data)
    {
        $data['Mapping'] = array();

        foreach ($data['seleki'] as $ekiData)
        {
            // 都道府県CD取得
            $todoufukenCd = $ekiData['TODOUFUKEN_CD'];
            // 都道府県が異なる場合
            if (array_key_exists($todoufukenCd, $data['Mapping']) == false)
            {
                // 都道府県データセット
                $data['Mapping']["$todoufukenCd"]['TODOUFUKEN_CD'] = $ekiData['TODOUFUKEN_CD'];
                $data['Mapping']["$todoufukenCd"]['TODOUFUKEN_NAME'] = $ekiData['TODOUFUKEN'];
                $data['Mapping']["$todoufukenCd"]['EKI_DATA'] = array();

            }

            // 共通駅CD取得
            $renrakuEkiCd = $ekiData['RENRAKU_EKI_CD'];
            // 駅が異なる場合
            if (array_key_exists($renrakuEkiCd, $data['Mapping']["$todoufukenCd"]['EKI_DATA']) == false)
            {
                // 駅データセット
                $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"]['EKI_CD'] = $ekiData['EKI_CD'];
                $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"]['EKI_NAME'] = $ekiData['EKI_NAME'];
                $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"]['REC_CNT'] = $ekiData['REC_CNT'];
                $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"]['IDO'] = number_format($ekiData['IDO']/921600,6);
                $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"]['KEIDO'] = number_format($ekiData['KEIDO']/921600,6);
                $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"][] = array();
            }

            // 沿線セット
            $ensenCd = 'ENSEN' . $ekiData['ENSEN_CD'];
            $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"]['ENSEN_DATA']["$ensenCd"]['ENSEN_NAME'] = $ekiData['ENSEN_NAME'];
        }
    }

    /**
     * パラメータ編集
     *
     * @access    private
     * @param     array
     * @return    string パラメータ
     */
    private function convParam($params)
    {
        // 入力文字の最後に"駅"の字がある場合は、取り除く
        $stWord = $params['StWord'];
        if (!empty($stWord) && $stWord != "駅")
        {
            if (mb_substr($stWord, -1, 1) == "駅")
            {
                $stWord = mb_substr($stWord, 0, mb_strlen($stWord)-1);
            }
        }
        // 全角・大文字へ変換
        return mb_strtoupper(mb_convert_kana($stWord,'AS','SJIS'));
    }

    /**
     * 入力ワードのログ出力
     *
     * @access    public
     * @param     array
     * @return    void
     */
    public function setKeywordLog($params)
    {
        
        $db_params = array();
        $sql = $this->generateSetKeywordLogSql($params, $db_params);
        
        $db = $this->getDbConnection();
        $this->startTransaction($db);
        try {
            $this->dbExecQuery($sql, $db_params, $db);
            $this->commitTransaction($db);
        } catch (Exception $e) {
            $this->rollbackTransaction($db);
            __log_debug(__FILE__, __LINE__, __METHOD__, $e->getMessage());
        }
        $db->closeConnection();
        return;
        
    }

    /**
     * 入力ワードログ出力用SQL文の取得
     *
     * @access    protected
     * @param     array
     * @param     array ※参照渡し
     * @return    string
     */
    private function generateSetKeywordLogSql($params, &$db_params)
    {
        
        $db_params['stWord'] = $this->convParam($params);
        $sql  = "";
        $sql .= "INSERT INTO";
        $sql .= "    W32DIRECT_KEYWORD(W32_KEYWORD)";
        $sql .= "VALUES";
        $sql .= "    ( :stWord )";
        
        return $sql;
    }

    /**
     * 集計テーブルの編集
     *
     * @access    public
     * @param     array
     * @return    void
     */
    public function editSum($params)
    {
        
        $db_params = array();
        $result = array();
        // 文字列編集
        $db_params['stWord'] = $this->convParam($params);
        
        $db = $this->getDbConnection();
        $this->startTransaction($db);
        try {
            // DELETE対象取得
            $sql = $this->generateGetDelSumSql();
            $result = $this->dbGetList($sql, $db_params);
            // 条件生成
            $where = $this->createWhereEditSum($result, "T54");
            // DELETE
            if ($where != "")
            {
                $sql = $this->generateDelSumSql($where);
                // 削除
                $this->dbExecQuery($sql, array(), $db);
            }
            
            // INSERT対象取得
            $sql = $this->generateGetInsSumSql();
            $result = $this->dbGetList($sql, $db_params);
            // 条件生成
            $where = $this->createWhereEditSum($result, "M03");
            // INSERT
            if ($where != "")
            {
                $sql = $this->generateInsSumSql($where);
                // 追加
                $this->dbExecQuery($sql, array(), $db);
            }
            $this->commitTransaction($db);
        } catch (Exception $e) {
            $this->rollbackTransaction($db);
            __log_debug(__FILE__, __LINE__, __METHOD__, $e->getMessage());
        }
        $db->closeConnection();
        
        return;
        
    }

    /**
     * 集計テーブルの追加検索用SQL取得
     *
     * @access    private
     * @return    string
     */
    private function generateGetInsSumSql()
    {
        
        $sql  = "";
        $sql .= "SELECT";
        $sql .= "    M03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
        $sql .= "    ,M03_ENSEN_CD AS ENSEN_CD";
        $sql .= "  FROM";
        $sql .= "    M03EKI M03";
        $sql .= " WHERE";
        $sql .= "    (";
        $sql .= "        UPPER(to_multi_byte(M03_EKI_NAME)) LIKE '%' || :stWord || '%'";
        $sql .= "        OR UPPER(to_multi_byte(M03_EKI_NAME)) LIKE '%' || :stWord || '駅%'";
        $sql .= "    )";
        $sql .= "    AND NOT EXISTS (";
        $sql .= "        SELECT";
        $sql .= "            'X'";
        $sql .= "          FROM";
        $sql .= "            T54EKI_SUM T54";
        $sql .= "         WHERE";
        $sql .= "            M03.M03_TODOUFUKEN_CD = T54.T54_TODOUFUKEN_CD";
        $sql .= "            AND M03.M03_ENSEN_CD = T54.T54_ENSEN_CD";
        $sql .= "    )";
        $sql .= " GROUP BY";
        $sql .= "    M03_TODOUFUKEN_CD";
        $sql .= "    ,M03_ENSEN_CD";
        
        return $sql;
    }

    /**
     * 集計テーブルの追加用SQL取得
     *
     * @access    private
     * @params    array
     * @return    string
     */
    private function generateInsSumSql($params)
    {
        
        $sql  = "";
        $sql .= "INSERT INTO T54EKI_SUM";
        $sql .= "(";
        $sql .= "    T54_TODOUFUKEN_CD";
        $sql .= "    ,T54_ENSEN_CD";
        $sql .= "    ,T54_EKI_CD";
        $sql .= "    ,T54_EKI_NAME";
        $sql .= "    ,T54_EKI_CNT";
        $sql .= "    ,T54_KOUSHIN";
        $sql .= "    ,T54_DATA_KUBUN";
        $sql .= ") ";
        $sql .= "SELECT";
        $sql .= "    TODOUFUKEN_CD";
        $sql .= "    ,ENSEN_CD";
        $sql .= "    ,EKI_CD";
        $sql .= "    ,EKI_NAME";
        $sql .= "    ,sum(EKI_HEYA_CNT) AS EKI_CNT";
        $sql .= "    ,sysdate AS KOSHIN";
        $sql .= "    ,0 AS DATA_KBN";
        $sql .= "  FROM";
        $sql .= "    (";
        $sql .= "        SELECT";
        $sql .= "            M03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD AS ENSEN_CD";
        $sql .= "            ,M03_EKI_CD AS EKI_CD";
        $sql .= "            ,M03_EKI_NAME AS EKI_NAME";
        $sql .= "            ,TGT_RENRAKU_CNT AS EKI_HEYA_CNT";
        $sql .= "          FROM";
        $sql .= "            M03EKI";
        $sql .= "            ,(";
        $sql .= "                SELECT";
        $sql .= "                    T63_RENRAKU_CD AS TGT_RENRAKU_CD";
        $sql .= "                    ,SUM(T63_COUNT) AS TGT_RENRAKU_CNT";
        $sql .= "                  FROM";
        $sql .= "                    T63RENRAKU_EKI_SUM ";
        $sql .= "                 WHERE";
        $sql .= "                    T63_DATA_KUBUN = 0";
        $sql .= "                 GROUP BY";
        $sql .= "                    T63_RENRAKU_CD";
        $sql .= "            )";
        $sql .= "         WHERE";
        $sql .= "            M03_RENRAKU_EKI_CD = TGT_RENRAKU_CD";
        $sql .= "            AND M03_RENRAKU_EKI_CD IS NOT NULL";
        $sql .= "            AND (" . $params . ")";
        $sql .= "         GROUP BY";
        $sql .= "            M03_TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD";
        $sql .= "            ,M03_EKI_CD";
        $sql .= "            ,M03_EKI_NAME";
        $sql .= "            ,TGT_RENRAKU_CNT";
        $sql .= "        UNION ALL";
        $sql .= "        SELECT";
        $sql .= "            M03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD AS ENSEN_CD";
        $sql .= "            ,M03_EKI_CD AS EKI_CD";
        $sql .= "            ,M03_EKI_NAME AS EKI_NAME";
        $sql .= "            ,COUNT(T03_HEYA_CD) AS EKI_HEYA_CNT";
        $sql .= "          FROM";
        $sql .= "            M03EKI";
        $sql .= "            ,T03WEB_HEYA";
        $sql .= "         WHERE";
        $sql .= "            M03_EKI_CD = T03_EKI_CD01";
        $sql .= "            AND M03_RENRAKU_EKI_CD IS NULL";
        $sql .= "            AND T03_RENRAKU_EKI_CD01 IS NULL";
        $sql .= "            AND (" . $params . ")";
        $sql .= "         GROUP BY";
        $sql .= "            M03_TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD";
        $sql .= "            ,M03_EKI_CD";
        $sql .= "            ,M03_EKI_NAME";
        $sql .= "        UNION ALL";
        $sql .= "        SELECT";
        $sql .= "            M03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD AS ENSEN_CD";
        $sql .= "            ,M03_EKI_CD AS EKI_CD";
        $sql .= "            ,M03_EKI_NAME AS EKI_NAME";
        $sql .= "            ,COUNT( T03_HEYA_CD ) AS EKI_HEYA_CNT";
        $sql .= "          FROM";
        $sql .= "            M03EKI";
        $sql .= "            ,T03WEB_HEYA";
        $sql .= "         WHERE";
        $sql .= "            M03_EKI_CD = T03_EKI_CD02";
        $sql .= "            AND M03_RENRAKU_EKI_CD IS NULL";
        $sql .= "            AND T03_RENRAKU_EKI_CD02 IS NULL";
        $sql .= "            AND (" . $params . ")";
        $sql .= "         GROUP BY";
        $sql .= "            M03_TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD";
        $sql .= "            ,M03_EKI_CD";
        $sql .= "            ,M03_EKI_NAME";
        $sql .= "        UNION ALL";
        $sql .= "        SELECT";
        $sql .= "            M03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD AS ENSEN_CD";
        $sql .= "            ,M03_EKI_CD AS EKI_CD";
        $sql .= "            ,M03_EKI_NAME AS EKI_NAME";
        $sql .= "            ,COUNT( T03_HEYA_CD ) AS EKI_HEYA_CNT";
        $sql .= "          FROM";
        $sql .= "            M03EKI";
        $sql .= "            ,T03WEB_HEYA";
        $sql .= "         WHERE";
        $sql .= "            M03_EKI_CD = T03_EKI_CD03";
        $sql .= "            AND M03_RENRAKU_EKI_CD IS NULL";
        $sql .= "            AND T03_RENRAKU_EKI_CD03 IS NULL";
        $sql .= "            AND (" . $params . ")";
        $sql .= "         GROUP BY";
        $sql .= "            M03_TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD";
        $sql .= "            ,M03_EKI_CD";
        $sql .= "            ,M03_EKI_NAME";
        $sql .= "    )";
        $sql .= " GROUP BY";
        $sql .= "    TODOUFUKEN_CD";
        $sql .= "    ,ENSEN_CD";
        $sql .= "    ,EKI_CD";
        $sql .= "    ,EKI_NAME";
        
        return $sql;
    }

    /**
     * 集計テーブルの削除検索用SQL取得
     *
     * @access    private
     * @return    string
     */
    private function generateGetDelSumSql()
    {
        
        $sql  = "";
        $sql .= "SELECT";
        $sql .= "    M03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
        $sql .= "    ,M03_ENSEN_CD AS ENSEN_CD";
        $sql .= "  FROM";
        $sql .= "    M03EKI M03";
        $sql .= " WHERE";
        $sql .= "    (";
        $sql .= "        UPPER(to_multi_byte(M03_EKI_NAME)) LIKE '%' || :stWord || '%'";
        $sql .= "        OR UPPER(to_multi_byte(M03_EKI_NAME)) LIKE '%' || :stWord || '駅%'";
        $sql .= "    )";
        $sql .= "    AND EXISTS (";
        $sql .= "        SELECT";
        $sql .= "            'X'";
        $sql .= "          FROM";
        $sql .= "            T54EKI_SUM T54";
        $sql .= "         WHERE";
        $sql .= "            M03.M03_TODOUFUKEN_CD = T54.T54_TODOUFUKEN_CD";
        $sql .= "            AND M03.M03_ENSEN_CD = T54.T54_ENSEN_CD";
        $sql .= "            AND T54.T54_DATA_KUBUN = 0";
        $sql .= "            AND T54.T54_KOUSHIN < SYSDATE - 30 / 1440";
        $sql .= "    )";
        $sql .= " GROUP BY";
        $sql .= "    M03_TODOUFUKEN_CD";
        $sql .= "    ,M03_ENSEN_CD";
        
        return $sql;
    }

    /**
     * 集計テーブルの削除用SQL取得
     *
     * @access    private
     * @params    array
     * @return    string
     */
    private function generateDelSumSql($params)
    {
        
        $sql  = "";
        $sql .= "DELETE FROM";
        $sql .= "    T54EKI_SUM";
        $sql .= " WHERE";
        $sql .= "    (" . $params . ")";
        $sql .= "    AND T54_DATA_KUBUN = 0";
        
        return $sql;
    }

    /**
     * 集計テーブル編集時のWHERE句生成
     *
     * @access    private
     * @params    array
     * @return    string
     */
    private function createWhereEditSum($params, $tblKbn)
    {
        
        $sql = "";
        foreach ($params as $ensen)
        {
            $sql .= " OR (" . $tblKbn . "_TODOUFUKEN_CD = '" . $ensen['TODOUFUKEN_CD'] . "' AND " . $tblKbn . "_ENSEN_CD = '" . $ensen['ENSEN_CD'] . "')";
        }
        if ($sql != "")
        {
            $sql = substr($sql, 3, strlen($sql));
        }
        
        return $sql;
    }

}
?>
